package danran.dbapi.utils;

import com.alibaba.druid.pool.DruidPooledConnection;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.baomidou.mybatisplus.extension.api.R;
import danran.dbapi.domain.DataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 操作数据库工具类
 *
 * @Classname JDBCUtil
 * @Description TODO
 * @Date 2022/1/17 9:47
 * @Created by RanCoder
 */
public class JDBCUtil {
    private static final Logger log = LoggerFactory.getLogger(JDBCUtil.class);

    /**
     * 获取数据库连接
     *
     * @param ds 数据源
     * @return 数据库连接
     */
    public static Connection connect(DataSource ds) throws SQLException {
        try {
            Class.forName(ds.getDriver());
            Connection conn =
                    DriverManager.getConnection(ds.getUrl(), ds.getUsername(), ds.getPassword());
            log.info("Successfully connected");
            return conn;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("JDBC" + ds.getDriver() + " Driver类找不到");
        }
    }

    /**
     * 执行sql查询
     *
     * @param conn 数据库连接
     * @param sql  查询sql
     * @return ResultSet
     * @throws SQLException when exception occurs
     */
    public static ResultSet query(Connection conn, String sql) throws SQLException {
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        return preparedStatement.executeQuery();
    }

    /**
     * @param ds 数据源
     * @return 数据库连接
     * @throws SQLException
     */
    public static Connection getConnection(DataSource ds) throws SQLException {
        try {
            Class.forName(ds.getDriver());
            Connection connection = DriverManager.getConnection(ds.getUrl(), ds.getUsername(), ds.getPassword());
            log.info("successfully connected");
            return connection;
        } catch (ClassNotFoundException e) {
            throw new RuntimeException("Please check whether the jdbc driver jar is missing, if missed copy the jdbc jar file to lib dir. " + e.getMessage());
        }
    }

    /**
     * 获取数据源的所有表名称
     *
     * @param conn 数据库连接
     * @param sql  查询sql
     * @return 数据库包含的所有表名称 <code>String</code>
     */
    public static List<String> getAllTables(Connection conn, String sql) {
        List<String> tables = new ArrayList<>();
        try {
            ResultSet resultSet = query(conn, sql);
            while (resultSet.next()) {
                tables.add(resultSet.getString(1));
            }
            return tables;
        } catch (Exception e) {
            log.error(e.getMessage(), e);
            return null;
        } finally {
            try {
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 获取指定表的列信息
     *
     * @param conn  数据库连接
     * @param type  数据库类型
     * @param table 数据表名称
     * @return 表列元数据 <code>JSONObject</code>
     */
    public static List<JSONObject> getRDBMSColumnProperties(Connection conn,
                                                            String type,
                                                            String table) {
        List<JSONObject> result = new ArrayList<>();
        PreparedStatement ps = null;
        try {
            String sql;
            switch (type) {
                case "POSTGRESQL": {
                    sql = "select * from \"" + table + "\" where 1 = 2";
                    break;
                }
                default: {
                    sql = "select * from " + table + " where 1 = 2";
                }
            }
            log.info(sql);
            ps = conn.prepareStatement(sql);
            ResultSetMetaData metaData = ps.executeQuery().getMetaData();
            for (int i = 0; i < metaData.getColumnCount(); i++) {
                JSONObject data = new JSONObject();
                data.put("fieldTypeName", metaData.getColumnName(i + 1));// 数据库字段类型
                data.put("fieldJavaTypeName", metaData.getColumnClassName(i + 1));
                String columnName = metaData.getColumnName(i + 1);
                columnName = columnName.contains(".") ? columnName.split("\\.")[1] : columnName;
                data.put("label", columnName);
                data.put("columnName", columnName);
                result.add(data);
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        } finally {
            try {
                if (ps != null) {
                    ps.close();
                }
                if (conn != null) {
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static Object executeSql(DataSource ds, String sql, List<Object> params) {
        log.info("sql = " + sql);
        log.info("params = " + JSON.toJSONString(params));
        DruidPooledConnection connection = null;
        try {
            connection = DruidPoolManager.getPooledConnection(ds);
            if (connection == null) {
                throw new RuntimeException("Connection is null.");
            }
            PreparedStatement ps = connection.prepareStatement(sql);
            // 参数注入
            for (int i = 0; i < params.size(); i++) {
                ps.setObject(i + 1, params.get(i));
            }

            boolean hasResult = ps.execute();
            if (hasResult) {
                ResultSet resultSet = ps.getResultSet();
                ResultSetMetaData metaData = resultSet.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<String> columnNames = new ArrayList<>();
                for (int i = 0; i < columnCount; i++) {
                    columnNames.add(metaData.getColumnName(i + 1));
                }
                List<JSONObject> data = new ArrayList<>();
                while (resultSet.next()) {
                    JSONObject jsonObject = new JSONObject();
                    columnNames.forEach(c -> {
                        try {
                            Object val = resultSet.getObject(c);
                            jsonObject.put(c, val);
                        } catch (SQLException e) {
                            e.printStackTrace();
                        }
                    });
                    data.add(jsonObject);
                }
                return data;
            } else {
                log.info(ps.getLargeUpdateCount() + " rows affected.");
                return ps.getLargeUpdateCount() + " rows affected.";
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}
